package sapling.dao;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import sapling.model.AreaLocation;

import java.util.List;

/**
 * Created by ever on 2016/10/9.
 */
@Transactional
public interface AreaLocationDao extends JpaRepository<AreaLocation, Long> {

    @Query("select o from AreaLocation o where o.serialNumber =?1")
    List<AreaLocation> areaLocation(String serialNumber);

    @Query(value = "SELECT MAX(serial_number) from area_location",nativeQuery = true)
    String number();

    @Query(value = "select * from area_location  where serial_number =?1",nativeQuery = true)
    AreaLocation findbyname(String serialNumber);

    @Query(value = "select count(*) from tree where landmassid in(select id from land_mass where area_location_id=?1)",nativeQuery = true)
    int getTreecount(Long areaLocation_id);

    @Query(value = "select tree_species from tree where landmassid in (select id from land_mass where area_location_id=?1) group by tree_species",nativeQuery = true)
    List<String> getAlltreename(Long areaid);

    @Query(value = " SELECT * FROM area_location ORDER BY serial_number",nativeQuery = true)
    List<AreaLocation> findByOrderBySerialNumberAsc();

    @Modifying
    @Query(value = "delete from tree_well where land_mass_id=?1",nativeQuery = true)
    void deltreewell(long land_mass_id);
}
